package sf.database.dialect.db;

import sf.core.DBField;
import sf.database.dbinfo.Feature;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.dialect.Keywords;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.DBMS;
import sf.jooq.JooqSupportDatabase;
import sf.querydsl.QueryDSLSupportDatabase;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;
import sf.tools.SystemUtils;

import java.sql.Connection;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * mysql
 */
public class MySqlDialect extends DBDialect {
    protected static final String META_ENGINE = "mysql-engine";

    protected static final String META_CHARSET = "mysql-charset";

    protected static final String META_INTLEN = "mysql-intlen";

    @Override
    public String getName() {
        return DBMS.mysql.name();
    }

    @Override
    public int getNumber() {
        return DBMS.mysql.getNumber();
    }

    public MySqlDialect() {
        this.keywords.addAll(Keywords.MYSQL);
        // 在MYSQL中 ||是逻辑运算符
        features.addAll(Arrays.asList(Feature.DBNAME_AS_SCHEMA, Feature.SUPPORT_INLINE_COMMENT, Feature.ALTER_FOR_EACH_COLUMN, Feature.NOT_FETCH_NEXT_AUTOINCREAMENTD, Feature.SUPPORT_LIMIT, Feature.COLUMN_DEF_ALLOW_NULL));
        setProperty(DBProperty.ADD_COLUMN, "ADD");
        setProperty(DBProperty.MODIFY_COLUMN, "MODIFY");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.SELECT_EXPRESSION, "select %s");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "``");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "SELECT LAST_INSERT_ID()");
        setProperty(DBProperty.INDEX_LENGTH_LIMIT, "767");
        setProperty(DBProperty.INDEX_LENGTH_LIMIT_FIX, "255");
        setProperty(DBProperty.INDEX_LENGTH_CHARESET_FIX, "charset=latin5");
        setProperty(DBProperty.DROP_INDEX_TABLE_PATTERN, "%1$s ON %2$s");
        setProperty(DBProperty.DROP_FK_PATTERN, "alter table %1$s drop foreign key %2$s");
        setProperty(DBProperty.NVL_FUNCTION, "ifnull(%1$s,%2$s)");
    }

    @Override
    public void evalFieldDefinitionLine(ColumnMapping mf, StringBuilder type) {
        String defaultValue = getColumnDefaultValue(mf);
        //无符号只有mysql支持
        if (mf.getColumnInfo() != null && mf.getColumnInfo().unsigned()) {
            type.append(" UNSIGNED");
        }
        if (mf.getColumn() != null && mf.getColumn().unique()) {
            type.append(" UNIQUE");
        }
        if ((mf.getColumn() != null && !mf.getColumn().nullable()) || mf.isPk()) {
            type.append(" NOT NULL");
        }
        if (StringUtils.isNotBlank(defaultValue)) {
            type.append(" DEFAULT ").append(defaultValue);
        }
    }

    @Override
    public String evalFieldType(ColumnMapping mf) {
        String type = "";
        int length = getColumnLength(mf);
        int scale = getColumnScale(mf);
        int precision = getColumnPrecision(mf);
        switch (mf.getSqlType()) {
            case Types.VARCHAR: {
                type = "VARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "TINYINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                type = "INT";
                break;
            }
            case Types.BIGINT: {
                type = "BIGINT";
                break;
            }
            case Types.FLOAT: {
                type = "FLOAT";
                break;
            }
            case Types.REAL:
            case Types.DOUBLE:
                type = "DOUBLE";
                break;
            case Types.BOOLEAN: {
                type = "TINYINT(1)";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    throw new UnsupportedOperationException("Unsupport type!");
                }
                break;
            }
            case Types.NUMERIC: {
                if (precision > 0) {
                    type = "NUMERIC(" + precision + "," + scale + ")";
                } else {
                    type = "NUMERIC(255,0)";
                }
                break;
            }
            case Types.DATE:
                type = "DATE";
                break;
            case Types.TIME:
            case Types.TIME_WITH_TIMEZONE:
                type = "TIME";
                break;
            case Types.TIMESTAMP:
            case Types.TIMESTAMP_WITH_TIMEZONE:
                type = "DATETIME";
                break;
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                if (mf.getColumn() != null) {
                    if (length > 65535 && length <= 16777215/*2的16次方减1*/) {
                        type = "MediumBlob";//最大长度 16777215 个字元(2^24-1)
                    } else if (length > 16777215/*2的24次方减1*/) {
                        type = "LongBlob";//最大长度4294967295个字元 (2^32-1)
                    }
                }
                if (StringUtils.isBlank(type)) {
                    //默认
                    type = "BLOB";//最大长度65535个字元(2^16-1)
                }
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                if (mf.getColumn() != null) {
                    if (length > 65535 && length <= 16777215/*2的16次方减1*/) {
                        type = "MediumText";//最大长度 16777215 个字元(2^24-1)
                    } else if (length > 16777215/*2的24次方减1*/) {
                        type = "LongText";//最大长度4294967295个字元 (2^32-1)
                    }
                }
                if (StringUtils.isBlank(type)) {
                    //默认
                    type = "TEXT";//最大长度65535个字元(2^16-1)
                }
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    public boolean createEntity(Connection conn, TableMapping en) {
        if (en.getTableName() == null) {
            throw new RuntimeException("table name is empty!");
        }
        StringBuilder sb = new StringBuilder("CREATE TABLE " + wrapKeyword(en.getTableName()) + "(");
        // 创建字段
        boolean delimiter = false;
        for (Map.Entry<DBField, ColumnMapping> entry : en.getSchemaMap().entrySet()) {
            ColumnMapping cm = entry.getValue();
            if (cm.getJpaTransient() != null) {
                continue;
            }
            sb.append(delimiter ? "," : "").append(SystemUtils.lineSeparator);
            sb.append(wrapKeyword(cm.getRawColumnName())).append(' ').append(evalFieldDefinition(cm));
            // 普通字段
            // 下面的关于Timestamp处理，是因为MySql中第一出现Timestamp的话，如果没有设定default，数据库默认会设置为CURRENT_TIMESTAMP

            if (cm.getGv() != null) {
                if (JavaTypeUtils.isNumberClass(cm.getClz())) {
                    sb.append(" AUTO_INCREMENT");
                }
            }

            if (StringUtils.isNotBlank(cm.getComment())) {
                sb.append(" COMMENT '").append(cm.getComment()).append('\'');
            }
            delimiter = true;
        }
        // 创建主键
        List<ColumnMapping> pks = en.getPkFields();
        if (!pks.isEmpty()) {
            sb.append(',').append(SystemUtils.lineSeparator);
            sb.append("PRIMARY KEY (");
            boolean f = false;
            for (ColumnMapping pk : pks) {
                sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                f = true;
            }
            sb.append(") ").append(SystemUtils.lineSeparator);
        }

        // 结束表字段设置
        sb.append(')');
        // 设置特殊引擎
        if (en.hasMeta(META_ENGINE)) {
            sb.append(" ENGINE=").append(en.getMeta(META_ENGINE));
        }
        // 编码
        if (en.hasMeta(META_CHARSET)) {
            sb.append(" CHARSET=").append(en.getMeta(META_CHARSET));
        }

        // 表名注释
        if (StringUtils.isNotBlank(en.getComment())) {
            sb.append(" COMMENT='").append(en.getComment()).append('\'');
        }

        List<String> sqls = new ArrayList<>();
        // 执行创建语句
        sqls.add(sb.toString());
        // 创建索引
        sqls.addAll(createIndexSql(en));
        // 创建约束
        sqls.addAll(createUniqueSql(en));

        execute(sqls, conn);

        // 创建关联表
        createRelation(conn, en);
        return true;
    }

    @Override
    public String uniqueSql(String tableName, String name, String[] columnNames) {
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(wrapKeyword(tableName)).append(" add unique ").append(name).append("(");
        for (int i = 0; i < columnNames.length; i++) {
            String column = columnNames[i];
            sb.append(i > 0 ? "," : "").append(wrapKeyword(column));
        }
        sb.append(")");
        return sb.toString();
    }

    @Override
    protected String escapeSqlValue(String value) {
        return StringUtils.replace(value, "'", "\\'");
    }

    @Override
    public String sqlTableDrop(String table) {
        return String.format("drop table if exists %s;", wrapKeyword(table));
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        return String.format("rename table  %s to %s;", wrapKeyword(oldName), wrapKeyword(newName));
    }

    @Override
    public String sqlColumnRename(String table, String oldColumnName, String newColumnName) {
        return null;
    }

    @Override
    public String sqlColumnAdd(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s add %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s modify %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnDrop(String table, String column) {
        return String.format("alter table %s drop %s;", wrapKeyword(table), wrapKeyword(column));
    }

    @Override
    public StringBuilder sqlPageList(StringBuilder sql, long offset, int limit) {
        if (offset > 0) {
            return sql.append(" limit ").append(offset).append(',').append(limit);
        } else {
            return sql.append(" limit ").append(limit);
        }
    }

    @Override
    public boolean supportsColumnPosition() {
        return true;
    }

    @Override
    public String getHibernateDialect() {
        return "org.hibernate.dialect.MySQLDialect";
    }

    @Override
    public String getCatalog(String catalog) {
        return catalog;
    }

    @Override
    public String getQueryDslDialect() {
        return QueryDSLSupportDatabase.MySQL;
    }

    @Override
    public String getJooqDialect() {
        return JooqSupportDatabase.MYSQL;
    }
}
